import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Properties;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;

public class OpenGaussUtil {
	private String url;
	private String user;
	private String password;
	private Connection connection;

	// 构造函数 - 接收连接参数
	public OpenGaussUtil(String url, String user, String password) {
		this.url = url;
		this.user = user;
		this.password = password;
	}

	// 连接到数据库
	public void connect() throws ClassNotFoundException, SQLException {
		// try {
		Class.forName("com.huawei.opengauss.jdbc.Driver"); // 加载openGauss驱动
		Properties props = new Properties();
		props.setProperty("user", user);
		props.setProperty("password", password);
		connection = DriverManager.getConnection(url, props);
		// System.out.println("Successfully connected to openGauss!");
		// } catch (SQLException e) {
		// System.out.println("【SQLException（SQL异常）】:❌ " + e.getMessage());
		// } catch (ClassNotFoundException e) {
		// System.out.println("【ClassNotFoundException（类未找到异常）】:❌ " + e.getMessage());
		// } catch (Exception e) {
		// System.out.println("【Exception（未知异常）】:❌ " + e.getClass().getName() + " - " +
		// e.getMessage());
		// }
	}

	// 断开数据库连接
	public void disconnect() throws Exception {
		if (connection != null && !connection.isClosed()) {
			connection.close();
		}
	}

	// 执行查询SQL并返回JSON结果
	public String executeQuery(String sql) {
		int code = 0;
		int count = 0;
		String msg = "";
		try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
			return resultSetToJson(rs);
		} catch (SQLException e) {
			code = -1;
			msg = "【SQLException（SQL异常）】:❌ " + e.getMessage() + "\n【SQL Info】: " + sql;
			return formatJsonString(code, count, msg, new JSONArray());
		} catch (Exception e) {
			code = -1;
			msg = "【Exception（未知异常）】:❌ " + e.getClass().getName() + " - " + e.getMessage() + "\n【SQL Info】: " + sql;
			return formatJsonString(code, count, msg, new JSONArray());
		}
	}

	// 将 ResultSet 转换为 JSON 字符串
	private String resultSetToJson(ResultSet rs) {
		int code = 0;
		int count = 0;
		String msg = "";
		String returnJsonString = "";
		JSONArray jsonArray = new JSONArray();
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while (rs.next()) {
				JSONObject jsonObject = new JSONObject();
				for (int i = 1; i <= columnCount; i++) {
					String columnName = metaData.getColumnLabel(i);
					// 处理日期类型的特殊格式化
					if ("java.sql.Timestamp".equals(metaData.getColumnClassName(i))
							|| "java.sql.Date".equals(metaData.getColumnClassName(i))) {
						Timestamp timestamp = rs.getTimestamp(i);
						jsonObject.put(columnName, formatTimestamp(timestamp));
					} else {
						jsonObject.put(columnName, rs.getObject(i));
					}
				}
				jsonArray.add(jsonObject);
			}
			code = 200;
			count = columnCount;
			returnJsonString = formatJsonString(code, count, msg, jsonArray);
		} catch (SQLException e) {
			code = -1;
			msg = "【SQLException（SQL异常）】:❌ " + e.getMessage();
			returnJsonString = formatJsonString(code, count, msg, new JSONArray());
			return returnJsonString;
		} catch (Exception e) {
			code = -1;
			msg = "【SQLException（SQL异常）】:❌ " + e.getMessage();
			returnJsonString = formatJsonString(code, count, msg, new JSONArray());
			return returnJsonString;
		}
		return returnJsonString;
	}

	// 格式化时间戳为 yyyy-MM-dd HH:mm:ss
	private String formatTimestamp(Timestamp timestamp) {
		if (timestamp == null)
			return null;
		return new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp);
	}

	// 格式化json字符串返回格式
	private String formatJsonString(int code, int count, String msg, JSONArray data) {
		JSONObject totalJsonObject = new JSONObject();
		totalJsonObject.put("code", code);
		totalJsonObject.put("count", count);
		totalJsonObject.put("msg", msg);
		totalJsonObject.put("data", data);
		return totalJsonObject.toJSONString();
	}

	// 执行更新操作（INSERT/UPDATE/DELETE）
	public String executeUpdate(String sql) {
		int code = 0;
		int count = 0;
		String msg = "";
		String returnJsonString = "";
		try (Statement stmt = connection.createStatement()) {
			count = stmt.executeUpdate(sql);
			msg = "【INFO】执行成功！ Affected rows: " + String.valueOf(stmt.executeUpdate(sql) + "\n【SQL Info】: " + sql);
			returnJsonString = formatJsonString(code, count, msg, new JSONArray());
			return returnJsonString;
		} catch (SQLException e) {
			msg = "【SQLException（SQL异常）】:❌ " + e.getMessage() + "\n【SQL Info】: " + sql;
			returnJsonString = formatJsonString(code, count, msg, new JSONArray());
			return returnJsonString;
		} catch (Exception e) {
			msg = "【Exception（未知异常）】:❌ " + e.getClass().getName() + " - " + e.getMessage() + "\n【SQL Info】: " + sql;
			returnJsonString = formatJsonString(code, count, msg, new JSONArray());
			return returnJsonString;
		}
	}
}
